========QUERY=========



SELECT	a.msgid, a.rcv_time, a.sent_time, a.hostname, 
	a.passedas, a.from, a.to,
	a.message_id, a.resent_message_id,
	a.mail_id,
	a.queue_id,
	convert_score(a.spamscore) AS spamscore,
	CAST(a.duration AS integer)
FROM	(SELECT	syslog.msgid, syslog.rcv_time, syslog.sent_time, syslog.hostname,
		substring(syslog.text from 'amavis% Passed #"%#", % <%> -> <%>, Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as passedas,
		substring(syslog.text from 'amavis% Passed %, % <#"%#"> -> <%>, Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as from,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <#"%#">, Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as to,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, Message-ID: <#"[^<>]+#">, (Resent-Message-ID: <#"%#">, |)mail_id: %, Hits: %, queued_as: %, % ms' for '#') as message_id,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, Message-ID: <%>, Resent-Message-ID: <#"%#">, mail_id: %, Hits: %, queued_as: %, % ms' for '#') as resent_message_id,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, Message-ID: <%>, mail_id: #"%#", Hits: %, queued_as: %, % ms' for '#') as mail_id,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, Message-ID: <%>, mail_id: %, Hits: #"%#", queued_as: %, % ms' for '#') as spamscore,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, Message-ID: <%>, mail_id: %, Hits: %, queued_as: #"%#", % ms' for '#') as queue_id,
		substring(syslog.text from 'amavis% Passed %, % <%> -> <%>, Message-ID: <%>, mail_id: %, Hits: %, queued_as: %, #"%#" ms' for '#') as duration,
		syslog.text,
		syslog.facility, syslog.priority, syslog.program
	 FROM public.log_syslog syslog
	 WHERE syslog.program = 'amavis'
	 AND syslog.facility = 'mail'
	 AND syslog.priority = 'notice'
	 AND syslog.text SIMILAR TO 'amavis% Passed [A-Za-z0-9]+, [][0-9.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>, (Resent-Message-ID: <[^<>]+>, |)mail_id: [^ ,]+, Hits: [-+0-9.,]+, queued_as: [^ ,]+, [0-9]+ ms'
	) a
;




========EXPLAIN=========

"Bitmap Heap Scan on log_syslog syslog  (cost=13196.27..51927.22 rows=1 width=270)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text))"
"  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~ '***:^(?:amavis.* Passed [A-Za-z0-9]+, [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>, (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+, queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
"  ->  BitmapAnd  (cost=13196.27..13196.27 rows=18957 width=0)"
"        ->  Bitmap Index Scan on "IX_log_syslog_program"  (cost=0.00..2235.95 rows=92323 width=0)"
"              Index Cond: ((program)::text = 'amavis'::text)"
"        ->  Bitmap Index Scan on "IX_log_syslog_facility"  (cost=0.00..10959.81 rows=463621 width=0)"
"              Index Cond: ((facility)::text = 'mail'::text)"

========EXPLAIN ANALYZE=========

"Bitmap Heap Scan on log_syslog syslog  (cost=13199.27..51930.22 rows=1 width=270) (actual time=8958.762..47095.587 rows=138 loops=1)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text))"
"  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~ '***:^(?:amavis.* Passed [A-Za-z0-9]+, [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>, (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+, queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
"  ->  BitmapAnd  (cost=13199.27..13199.27 rows=18957 width=0) (actual time=411.696..411.696 rows=0 loops=1)"
"        ->  Bitmap Index Scan on "IX_log_syslog_program"  (cost=0.00..2238.95 rows=92323 width=0) (actual time=111.073..111.073 rows=97779 loops=1)"
"              Index Cond: ((program)::text = 'amavis'::text)"
"        ->  Bitmap Index Scan on "IX_log_syslog_facility"  (cost=0.00..10959.81 rows=463621 width=0) (actual time=291.897..291.897 rows=466392 loops=1)"
"              Index Cond: ((facility)::text = 'mail'::text)"
"Total runtime: 47096.687 ms"



==================================
========= WITHOUT REGEX ==========
Just comment out the SIMILAR TO line.

========EXPLAIN=========

"Bitmap Heap Scan on log_syslog syslog  (cost=13208.13..52496.93 rows=11867 width=270)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text))"
"  Filter: ((priority)::text = 'notice'::text)"
"  ->  BitmapAnd  (cost=13208.13..13208.13 rows=18957 width=0)"
"        ->  Bitmap Index Scan on "IX_log_syslog_program"  (cost=0.00..2235.95 rows=92323 width=0)"
"              Index Cond: ((program)::text = 'amavis'::text)"
"        ->  Bitmap Index Scan on "IX_log_syslog_facility"  (cost=0.00..10959.81 rows=463621 width=0)"
"              Index Cond: ((facility)::text = 'mail'::text)"

========EXPLAIN ANALYZE=========

"Bitmap Heap Scan on log_syslog syslog  (cost=13214.13..52502.93 rows=11867 width=270) (actual time=731.061..55188.298 rows=10045 loops=1)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text))"
"  Filter: ((priority)::text = 'notice'::text)"
"  ->  BitmapAnd  (cost=13214.13..13214.13 rows=18957 width=0) (actual time=672.800..672.800 rows=0 loops=1)"
"        ->  Bitmap Index Scan on "IX_log_syslog_program"  (cost=0.00..2238.95 rows=92323 width=0) (actual time=103.780..103.780 rows=97784 loops=1)"
"              Index Cond: ((program)::text = 'amavis'::text)"
"        ->  Bitmap Index Scan on "IX_log_syslog_facility"  (cost=0.00..10962.81 rows=463621 width=0) (actual time=560.112..560.112 rows=466409 loops=1)"
"              Index Cond: ((facility)::text = 'mail'::text)"
"Total runtime: 55201.639 ms"


=========================================
========= AFTER VACUUM ANALYZE ==========

========EXPLAIN ANALYZE=========

"Bitmap Heap Scan on log_syslog syslog  (cost=12773.57..47836.81 rows=1 width=267) (actual time=12377.693..33147.458 rows=142 loops=1)"
"  Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text))"
"  Filter: (((priority)::text = 'notice'::text) AND ((text)::text ~ '***:^(?:amavis.* Passed [A-Za-z0-9]+, [][0-9\\.]* <[^<>]+> -> <[^<>]+>, Message-ID: <[^<>]+>, (Resent-Message-ID: <[^<>]+>, |)mail.id: [^ ,]+, Hits: [-+0-9\\.,]+, queued.as: [^ ,]+, [0-9]+ ms)$'::text))"
"  ->  BitmapAnd  (cost=12773.57..12773.57 rows=16636 width=0) (actual time=666.590..666.590 rows=0 loops=1)"
"        ->  Bitmap Index Scan on "IX_log_syslog_program"  (cost=0.00..1998.97 rows=82632 width=0) (actual time=191.218..191.218 rows=84828 loops=1)"
"              Index Cond: ((program)::text = 'amavis'::text)"
"        ->  Bitmap Index Scan on "IX_log_syslog_facility"  (cost=0.00..10774.10 rows=456489 width=0) (actual time=466.576..466.576 rows=453525 loops=1)"
"              Index Cond: ((facility)::text = 'mail'::text)"
"Total runtime: 33155.708 ms"